package com.carmaintain.db;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;

import com.carmaintain.common.Constants;
import com.carmaintain.common.Utils;
import com.carmaintain.config.Configs;
import com.carmaintain.models.CarInfo;
import com.carmaintain.models.Customer;
import com.carmaintain.models.MaintainHistory;
import com.carmaintain.models.MaintainItem;
import com.carmaintain.models.Message;
import com.carmaintain.models.PlaceComment;
import com.carmaintain.models.Request;
import com.carmaintain.models.RequestMessage;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

public class CustomerLib {	
	/**
	 * 评价经营场所
	 */
	public static boolean commentPlace(String customerId, String placeId, String generalSatisfaction, 
			String serviceSatisfaction, String comment) throws Exception{
		int result;
		
		String sql = "" +
				" INSERT INTO" +
				"	" + Constants.TABLE_PLACE_COMMENT + 
				"	(place_id, customer_id, general_satisfaction, service_satisfaction, comment) " +
				" VALUES " +
				"	('" + StringEscapeUtils.escapeSql(placeId) + "', " +
				"	'" + StringEscapeUtils.escapeSql(customerId) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(generalSatisfaction) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(serviceSatisfaction) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(comment) + "')";
		result = DBConn.update(sql);
		
		return (result > 0);
	}
	
	/**
	 * 取得经营场所评价
	 */
	public static List<PlaceComment> getPlaceComments(String placeId) throws Exception{
		List<PlaceComment> placeComments = null;
		PlaceComment placeComment = null;
		ResultSet result;
		String sql = "";
		
		sql = "" +
				" 	SELECT " +
				"		A.id, A.general_satisfaction, A.service_satisfaction, A.comment, A.comment_time, B.name " +
				" 	FROM " + Constants.TABLE_PLACE_COMMENT + " A " +
				"		LEFT JOIN " + Constants.TABLE_CUSTOMER + " B " +
				"		ON A.customer_id = B.user_id " +
				" 	WHERE A.place_id = '" + StringEscapeUtils.escapeSql(placeId) + "'" +
				"	ORDER BY A.comment_time DESC " +
				"	LIMIT 0,3 ";
		result = DBConn.query(sql);
		
		if (result.first()) {
			placeComments = new ArrayList<PlaceComment>();
			
			do {
				placeComment = new PlaceComment();
				placeComment.setId(result.getString("id"));
				placeComment.setGeneralSatisfaction(result.getString("general_satisfaction"));
				placeComment.setServiceSatisfaction(result.getString("service_satisfaction"));
				placeComment.setComment(result.getString("comment"));
				placeComment.setCommentTime(result.getDate("comment_time").toString());
				placeComment.setCustomerName(result.getString("name"));
				
				placeComments.add(placeComment);
			} while(result.next());
		}
		
		return placeComments;
	}
	
	/**
	 * 取得我的车辆列表
	 */
	public static List<CarInfo> getMyCars(String customerId) throws Exception{
		List<CarInfo> myCars = null;
		CarInfo car = null;
		ResultSet result;
		String sql = "";
		
		sql = "" +
				" 	SELECT " +
				"		license_number " +
				" 	FROM " + Constants.TABLE_CAR_INFO +
				" 	WHERE customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "'" +
				"	ORDER BY buy_date DESC ";
		result = DBConn.query(sql);
		
		if (result.first()) {
			myCars = new ArrayList<CarInfo>();
			
			do {
				car = new CarInfo();
				car.setCarLicense(result.getString("license_number"));
				
				myCars.add(car);
			} while(result.next());
		}
		
		return myCars;
	}
	
	/**
	 * 取得我的车辆详情列表
	 */
	public static List<CarInfo> getMyCarsDetail(String customerId, String carLicense) throws Exception{
		List<CarInfo> myCars = null;
		CarInfo car = null;
		ResultSet result;
		String sql = "";
		String whereStr = "";
		
		if (!Utils.empty(carLicense)) {
			whereStr += "	AND license_number = '" + StringEscapeUtils.escapeSql(carLicense) + "'";
		}
		
		
		sql = "" +
				" 	SELECT " +
				"		license_number, model, displacement, buy_date, photo " +
				" 	FROM " + Constants.TABLE_CAR_INFO +
				" 	WHERE customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "'" +
				whereStr +
				"	ORDER BY buy_date DESC ";
		result = DBConn.query(sql);
		
		if (result.first()) {
			myCars = new ArrayList<CarInfo>();
			
			do {
				car = new CarInfo();
				car.setCarLicense(result.getString("license_number"));
				car.setCarModel(result.getString("model"));
				car.setCarDisplacement(result.getString("displacement"));
				car.setBuyDate(result.getString("buy_date"));
				car.setCarPhoto(Configs.uploadPath.replaceAll("\\\\", "/") + result.getString("photo"));
				
				myCars.add(car);
			} while(result.next());
		}
		
		return myCars;
	}
	
	/**
	 * 添加车辆信息
	 */
	public static int addCarInfo(String customerId, String licenseNumber, String carModel, String displacement, 
			String buyDate, String photo) throws Exception{
		
		String sql = "" +
				" INSERT INTO" +
				"	" + Constants.TABLE_CAR_INFO + 
				"	(license_number, customer_id, model, displacement, buy_date, photo) " +
				" VALUES " +
				"	('" + StringEscapeUtils.escapeSql(licenseNumber) + "', " +
				"	'" + StringEscapeUtils.escapeSql(customerId) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(carModel) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(displacement) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(buyDate) + "', " + 
				"	'" + StringEscapeUtils.escapeSql(photo) +  "')";
		try {
			DBConn.update(sql);
		} catch (MySQLIntegrityConstraintViolationException e) {
			return Constants.CODE_LICENSE_EXIST;
		}
		
		return Constants.CODE_SUCCESS;
	}
	
	/**
	 * 修改车辆信息
	 */
	public static int modifyCarInfo(String customerId, String licenseNumber, String carModel, String displacement, 
			String buyDate, String photo) throws Exception{
		
		String sql = "" +
				" UPDATE" +
				"	" + Constants.TABLE_CAR_INFO + 
				" SET " +
				"	model = '" + StringEscapeUtils.escapeSql(carModel) + "', " + 
				"	displacement = '" + StringEscapeUtils.escapeSql(displacement) + "', " + 
				"	buy_date = '" + StringEscapeUtils.escapeSql(buyDate) + "', " + 
				"	photo = '" + StringEscapeUtils.escapeSql(photo) +  "'" +
				" WHERE license_number = '" + StringEscapeUtils.escapeSql(licenseNumber) + "'";
		try {
			DBConn.update(sql);
		} catch (MySQLIntegrityConstraintViolationException e) {
			return Constants.CODE_LICENSE_EXIST;
		}
		
		return Constants.CODE_SUCCESS;
	}
	
	public static List<MaintainHistory> getMaintainOrders(String customerId, String carLicense, String orderStatus) throws Exception{
		List<MaintainHistory> histories = null;
		MaintainHistory history = null;
		ResultSet result = null;
		String whereStr = "";
		
		if ("complete".equals(orderStatus)) {
			whereStr += " AND status = " + Constants.MAINTAIN_STATUS_COMMENT_COMPLETE;
		} else if ("waiting".equals(orderStatus)) {
			whereStr += " AND status != " + Constants.MAINTAIN_STATUS_COMMENT_COMPLETE;
		}
		
		String sql = "" +
				" SELECT " +
				" 	order_id, license_number, maintain_date, mileage, maintain_fee, status " +
				" FROM " + Constants.TABLE_MAINTAIN_ORDER + 
				" WHERE license_number = '" + StringEscapeUtils.escapeSql(carLicense) + "'" +
				whereStr;
		result = DBConn.query(sql);
		if (result.first()) {
			histories = new ArrayList<MaintainHistory>();
			do {
				history = new MaintainHistory();
				
				history.setId(result.getString("order_id"));
				history.setLicenseNumber(result.getString("license_number"));
				history.setMaintainDate(result.getDate("maintain_date").toString());
				history.setMileage(result.getString("mileage"));
				history.setMaintainFee(result.getString("maintain_fee"));
				history.setStatus(result.getString("status"));
				
				histories.add(history);
			} while(result.next());
		}
		
		return histories;
	}
	
	/**
	 * 取得养护订单详情
	 */
	public static MaintainHistory getMaintainOrderDetail(String customerId, String orderId) throws Exception{
		MaintainHistory ret = null;
		ResultSet result;
		List<MaintainItem> maintainItems = null;
		MaintainItem maintainItem = null;
		
		String sql = "" +
				" 	SELECT " +
				"		A.order_id, A.license_number, A.merchant_id, " +
				"		C.name, C.mobile, A.customer_name, A.customer_mobile, " +
				"		A.mileage, A.maintain_date, A.place_id, D.address, A.maintain_fee, A.comment, A.status, B.model " +
				" 	FROM " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				"		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"		LEFT JOIN " + Constants.TABLE_CUSTOMER + " C " +
				"		ON B.customer_id = C.user_id " +
				"		LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " D " +
				"		ON A.place_id = D.id " +
				"	WHERE A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'" +
				"	AND C.user_id = '" + StringEscapeUtils.escapeSql(customerId) + "'";
		
		result = DBConn.query(sql);
		
		if (result.first()) {			
			ret = new MaintainHistory();
			
			ret.setId(result.getString("order_id"));
			ret.setLicenseNumber(result.getString("license_number"));
			ret.setMileage(result.getString("mileage"));
			ret.setMaintainDate(result.getString("maintain_date"));
			ret.setPlaceId(result.getString("place_id"));
			ret.setAddress(result.getString("address"));
			ret.setMaintainFee(result.getString("maintain_fee"));
			ret.setComment(result.getString("comment"));
			ret.setStatus(result.getString("status"));
			
			//取得维护项目列表
			sql = "" +
					" SELECT " +
					"	A.item_id, A.item_brand, A.item_type, A.item_period, A.item_model, B.brand_id, B.type_id, B.period_id, B.model, " +
					"	C.brand_name, D.period_name, E.type_name " +
					" FROM " + Constants.TABLE_MAINTAIN_ITEM + " A " +
					" 	LEFT JOIN " + Constants.TABLE_ITEM + " B " +
					"	ON A.item_id = B.id " +
					"	LEFT JOIN " + Constants.TABLE_ITEM_BRAND + " C " +
					"	ON B.brand_id = C.id " +
					"	LEFT JOIN " + Constants.TABLE_ITEM_PERIOD + " D " +
					"	ON B.period_id = D.id " +
					"	LEFT JOIN " + Constants.TABLE_ITEM_TYPE + " E " +
					"	ON B.type_id = E.id " +
					" WHERE A.order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'";
			result = DBConn.query(sql);
			if (result.first()) {
				maintainItems = new ArrayList<MaintainItem>();
				do {
					maintainItem = new MaintainItem();
					
					if (!Utils.empty(result.getString("item_id"))) {
						maintainItem.setItemId(result.getString("item_id"));
						maintainItem.setItemBrand(result.getString("brand_id"));
						maintainItem.setItemType(result.getString("type_id"));
						maintainItem.setItemPeriod(result.getString("period_id"));
						maintainItem.setItemModel(result.getString("model"));
						
						maintainItem.setItemBrandName(result.getString("brand_name"));
						maintainItem.setItemTypeName(result.getString("type_name"));
						maintainItem.setItemPeriodName(result.getString("period_name"));
					} else {
						maintainItem.setItemBrandName(result.getString("item_brand"));
						maintainItem.setItemTypeName(result.getString("item_type"));
						maintainItem.setItemPeriodName(result.getString("item_period"));
						maintainItem.setItemModel(result.getString("item_model"));
					}
					
					maintainItems.add(maintainItem);
				} while(result.next());
				
				ret.setMaintainItems(maintainItems);
			}
		}
		
		return ret;
	}
	
	/**
	 * 取消养护订单
	 */
	public static boolean cancelMaintainOrder(String customerId, String orderId) throws Exception{
		int result = 0;
		
		String sql = "" +
				" 	UPDATE " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				" 		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"	SET status = " + Constants.MAINTAIN_STATUS_CANCELED +
				"	WHERE order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'" +
				"	AND A.status = " + Constants.MAINTAIN_STATUS_WAIT +
				"	AND B.customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "'";
		
		result = DBConn.update(sql);
		
		return result > 0;
	}
	
	/**
	 * 修改养护日期
	 */
	public static boolean changeMaintainDate(String customerId, String orderId, String maintainDate) throws Exception{
		int result = 0;
		
		String sql = "" +
				" 	UPDATE " + Constants.TABLE_MAINTAIN_ORDER + " A " +
				" 		LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"		ON A.license_number = B.license_number " +
				"	SET maintain_date = '" + StringEscapeUtils.escapeSql(maintainDate) + "'" +
				"	WHERE order_id = '" + StringEscapeUtils.escapeSql(orderId) + "'" +
				"	AND A.status = " + Constants.MAINTAIN_STATUS_WAIT +
				"	AND B.customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "'";
		
		result = DBConn.update(sql);
		
		return result > 0;
	}
	
	/**
	 * 取得我的信息
	 */
	public static Customer getCustomerProfile(String customerId) throws Exception{
		Customer profile = null;
		ResultSet result;
		String sql = "";
		
		sql = "" +
				" 	SELECT " +
				"		user_id, mobile, email, name, city, wangwang " +
				" 	FROM " + Constants.TABLE_CUSTOMER +
				" 	WHERE user_id = '" + StringEscapeUtils.escapeSql(customerId) + "'";
		result = DBConn.query(sql);
		
		if (result.first()) {
			profile = new Customer();

			profile.setId(result.getString("user_id"));
			profile.setMobile(result.getString("mobile"));
			profile.setEmail(result.getString("email"));
			profile.setName(result.getString("name"));
			profile.setCity(result.getString("city"));
			profile.setWangwang(result.getString("wangwang"));
		}
		
		return profile;
	}
	
	/**
	 * 修改邮箱
	 */
	public static boolean modifyEmail(String customerId, String newEmail, String password) throws Exception{
		int result;
		String sql = "";
		
		sql = "" +
				" 	UPDATE " + Constants.TABLE_CUSTOMER +
				"	SET " +
				"		email = '" + StringEscapeUtils.escapeSql(newEmail) + "'" +
				" 	WHERE user_id = '" + StringEscapeUtils.escapeSql(customerId) + "'" +
				"	AND password = '" + StringEscapeUtils.escapeSql(Utils.md5(password)) + "'";
		result = DBConn.update(sql);
		
		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * 修改密码
	 */
	public static boolean modifyPassword(String customerId, String oldPassword, String newPassword) throws Exception{
		int result;
		String sql = "";
		
		sql = "" +
				" 	UPDATE " + Constants.TABLE_CUSTOMER +
				"	SET " +
				"		password = '" + StringEscapeUtils.escapeSql(Utils.md5(newPassword)) + "'" +
				" 	WHERE user_id = '" + StringEscapeUtils.escapeSql(customerId) + "'" +
				"	AND password = '" + StringEscapeUtils.escapeSql(Utils.md5(oldPassword)) + "'";
		result = DBConn.update(sql);
		
		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * 修改用户信息
	 */
	public static boolean modifyProfile(String customerId, String mobile, String city, String wangwang) throws Exception{
		int result;
		String sql = "";
		
		sql = "" +
				" 	UPDATE " + Constants.TABLE_CUSTOMER +
				"	SET " +
				"		mobile = '" + StringEscapeUtils.escapeSql(mobile) + "', " +
				"		city = '" + StringEscapeUtils.escapeSql(city) + "', " +
				"		wangwang = '" + StringEscapeUtils.escapeSql(wangwang) + "'" +
				" 	WHERE user_id = '" + StringEscapeUtils.escapeSql(customerId) + "'";
		result = DBConn.update(sql);
		
		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}
	
	/**
	 * 取得我的消息
	 */
	public static Map<String, Object> getMyMessages(String customerId, String type, int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<Message> ret = null;
		ResultSet result;
		Message msg = null;
		String sql = "";
		String countSql = "";
		String limitStr = "";
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		if (type.equals(Constants.MSG_TYPE_RECEIVE + "")) {
			sql = "" +
					" SELECT " +
					"	id, sender_id as merchant_id, msg_title, status, receive_date as date, B.corporation_name as name " +
					" FROM " + Constants.TABLE_MESSAGE_RECEIVE + " A " +
					" 	LEFT JOIN " + Constants.TABLE_MERCHANT + " B " +
					"	ON A.sender_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND receiver_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
					" ORDER BY date DESC " +
					limitStr;
			
			countSql = "" +
					" SELECT count(1) as total_num " +
					" FROM " + Constants.TABLE_MESSAGE_RECEIVE +
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND receiver_id = '" + StringEscapeUtils.escapeSql(customerId) + "' ";
		} else if (type.equals(Constants.MSG_TYPE_DRAFT + "")) {
			sql = "" +
					" SELECT " +
					"	id, receiver_id as merchant_id, msg_title, status, modify_date as date, B.corporation_name AS name " +
					" FROM " + Constants.TABLE_MESSAGE_SEND + " A " +
					" 	LEFT JOIN " + Constants.TABLE_MERCHANT + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
					"	AND status = " + Constants.MSG_STATUS_WRITING +
					" ORDER BY date DESC " +
					limitStr;
			
			countSql = "" +
					" SELECT count(1) as total_num " +
					" FROM " + Constants.TABLE_MESSAGE_SEND +
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
					"	AND status = " + Constants.MSG_STATUS_WRITING;
		} else if (type.equals(Constants.MSG_TYPE_SENT + "")) {
			sql = "" +
					" SELECT " +
					"	id, receiver_id AS merchant_id, msg_title, status, modify_date as date, B.corporation_name AS name" +
					" FROM " + Constants.TABLE_MESSAGE_SEND + " A " +
					" 	LEFT JOIN " + Constants.TABLE_MERCHANT + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
					"	AND status = " + Constants.MSG_STATUS_SENT +
					" ORDER BY date DESC " +
					limitStr;
			
			countSql = "" +
					" SELECT count(1) as total_num " +
					" FROM " + Constants.TABLE_MESSAGE_SEND +
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
					"	AND status = " + Constants.MSG_STATUS_SENT;
		}
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<Message>();
			
			do {
				msg = new Message();
				msg.setMessageId(result.getString("id"));
				if (type.equals(Constants.MSG_TYPE_RECEIVE + "")) {
					msg.setSenderId(result.getString("merchant_id"));
					msg.setSenderName(result.getString("name"));
				} else {
					msg.setReceiverId(result.getString("merchant_id"));
					msg.setReceiverName(result.getString("name"));
				}
				msg.setTitle(result.getString("msg_title"));
				msg.setStatus(result.getString("status"));
				msg.setDate(result.getDate("date").toString() + " " + result.getTime("date").toString());
				
				ret.add(msg);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		result = DBConn.query(countSql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		return dataMap;
	}
	
	/**
	 * 请求帮助
	 */
	public static boolean requestHelp(String customerId, String helpType, long limit, String content, String address, String gps, 
			String contactInfo, String carLicense) throws Exception{
		int result;
		
		String sql = "" +
				" INSERT INTO " + Constants.TABLE_REQUEST +
				" 	(customer_id, type, help_type, request_content, request_date, request_limit, car_license, address, gps_location, status) " +
				" VALUES (" +
				"	'" + StringEscapeUtils.escapeSql(customerId) + "', " +
				"	" + Constants.REQUEST_TYPE_HELP + ", " +
				"	'" + StringEscapeUtils.escapeSql(helpType) + "', " +
				"	'" + StringEscapeUtils.escapeSql(content) + "', " +
				"	'" + Utils.getStrTime(System.currentTimeMillis()) + "', " +
				"	'" + (System.currentTimeMillis() + limit * 3600 * 1000) + "', " +
				"	'" + StringEscapeUtils.escapeSql(carLicense) + "', " +
				"	'" + StringEscapeUtils.escapeSql(address) + "', " +
				"	'" + StringEscapeUtils.escapeSql(gps) + "', " +
					Constants.REQUEST_STATUS_WAITING + ")";
		
		result = DBConn.update(sql);
		
		if (result > 0) {
			return true;
		}
		
		return false;
	}
	
	/**
	 * 请求养护
	 */
	public static boolean requestMaintain(String customerId, String content, String carLicense, String mileage) throws Exception{
		int result;
		
		String sql = "" +
				" INSERT INTO " + Constants.TABLE_REQUEST +
				" 	(customer_id, type, car_license, current_mileage, request_content, request_date, status) " +
				" VALUES (" +
				"	'" + StringEscapeUtils.escapeSql(customerId) + "', " +
				"	" + Constants.REQUEST_TYPE_MAINTAIN + ", " +
				"	'" + StringEscapeUtils.escapeSql(carLicense) + "', " +
				"	'" + StringEscapeUtils.escapeSql(mileage) + "', " +
				"	'" + StringEscapeUtils.escapeSql(content) + "', " +
				"	'" + Utils.getStrTime(System.currentTimeMillis()) + "', " +
					Constants.REQUEST_STATUS_WAITING + ")";
		
		result = DBConn.update(sql);
		
		if (result > 0) {
			return true;
		}
		
		return false;
	}
	
	
	/**
	 * 取得我的需求
	 */
	public static Map<String, Object> getMyRequests(String customerId, int page, int pageSize, String type) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<Request> ret = null;
		ResultSet result;
		Request request = null;
		String sql = "";
		String countSql = "";
		String limitStr = "";
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		sql = "" +
				" SELECT " +
				"	id, type, request_content, request_date, request_limit " +
				" FROM " + Constants.TABLE_REQUEST +
				" WHERE customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
				" AND type = '" + StringEscapeUtils.escapeSql(type) + "' " +
				" ORDER BY request_date DESC " +
				limitStr;
		
		countSql = "" +
				" SELECT " +
				"	count(1) AS total_num " +
				" FROM " + Constants.TABLE_REQUEST +
				" WHERE customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
				" AND type = '" + StringEscapeUtils.escapeSql(type) + "' ";
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<Request>();
			
			do {
				request = new Request();
				request.setId(result.getString("id"));
				request.setType(result.getString("type"));
				request.setContent(result.getString("request_content"));
				request.setRemainTime((result.getLong("request_limit") - System.currentTimeMillis()) + "");
				request.setDate(result.getDate("request_date").toString() + " " + result.getTime("request_date").toString());
				
				ret.add(request);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		result = DBConn.query(countSql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		return dataMap;
	}
	
	/**
	 * 取得我的评价
	 */
	public static Map<String, Object> getMyComments(String customerId, int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<PlaceComment> ret = null;
		ResultSet result;
		PlaceComment comment = null;
		String sql = "";
		String countSql = "";
		String limitStr = "";
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		sql = "" +
				" SELECT " +
				"	A.id, service_satisfaction, comment, B.place_name " +
				" FROM " + Constants.TABLE_PLACE_COMMENT + " A " +
				" 	LEFT JOIN " + Constants.TABLE_MERCHANT_PLACE + " B " +
				" 	ON A.place_id = B.id " +
				" WHERE customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
				" ORDER BY comment_time DESC " +
				limitStr;
		
		countSql = "" +
				" SELECT " +
				"	count(1) AS total_num " +
				" FROM " + Constants.TABLE_PLACE_COMMENT +
				" WHERE customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "' ";
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<PlaceComment>();
			
			do {
				comment = new PlaceComment();
				comment.setId(result.getString("id"));
				comment.setServiceSatisfaction(result.getString("service_satisfaction"));
				comment.setComment(result.getString("comment"));
				comment.setPlaceName(result.getString("place_name"));
				
				ret.add(comment);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		result = DBConn.query(countSql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		return dataMap;
	}
	
	/**
	 * 取得消息详细
	 */
	public static Message getMessageDetail(String messageId, String customerId, String type) throws Exception{
		ResultSet result;
		String sql = "";
		Message msg = null;
		
		if (type.equals(Constants.MSG_TYPE_RECEIVE + "")) {
			sql = "" +
					" SELECT " +
					"	id, sender_id AS merchant_id, msg_title, msg_content, status, receive_date as date, B.corporation_name " +
					" FROM " + Constants.TABLE_MESSAGE_RECEIVE + " A " +
					" 	LEFT JOIN " + Constants.TABLE_MERCHANT + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER+
					"	AND receiver_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
					"	AND id = " + StringEscapeUtils.escapeSql(messageId) +
					" ORDER BY date DESC ";
		} else if (type.equals(Constants.MSG_TYPE_DRAFT + "")) {
			sql = "" +
					" SELECT " +
					"	id, receiver_id AS merchant_id, msg_title, msg_content, status, modify_date as date, B.corporation_name " +
					" FROM " + Constants.TABLE_MESSAGE_SEND + " A " +
					" 	LEFT JOIN " + Constants.TABLE_MERCHANT + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
					"	AND id = " + StringEscapeUtils.escapeSql(messageId) +
					"	AND status = " + Constants.MSG_STATUS_WRITING;
		} else if (type.equals(Constants.MSG_TYPE_SENT + "")) {
			sql = "" +
					" SELECT " +
					"	id, receiver_id AS merchant_id, msg_title, msg_content, status, modify_date as date, B.corporation_name" +
					" FROM " + Constants.TABLE_MESSAGE_SEND + " A " +
					" 	LEFT JOIN " + Constants.TABLE_MERCHANT + " B " +
					"	ON A.receiver_id = B.user_id " +
					" WHERE type = " + Constants.MSG_TYPE_CUSTOMER_TO_MERCHANT +
					"	AND sender_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
					"	AND id = " + StringEscapeUtils.escapeSql(messageId) +
					"	AND status = " + Constants.MSG_STATUS_SENT;
		}
		result = DBConn.query(sql);
		
		if (result.first()) {
			msg = new Message();
			msg.setMessageId(result.getString("id"));
			if (type.equals(Constants.MSG_TYPE_RECEIVE + "")) {
				msg.setSenderId(result.getString("merchant_id"));
				msg.setSenderName(result.getString("corporation_name"));
			} else {
				msg.setReceiverId(result.getString("merchant_id"));
				msg.setReceiverName(result.getString("corporation_name"));
			}
			msg.setTitle(result.getString("msg_title"));
			msg.setContent(result.getString("msg_content"));
			msg.setStatus(result.getString("status"));
			msg.setDate(result.getDate("date").toString() + " " + result.getTime("date").toString());
		}
		
		return msg;
	}
	
	/**
	 * 阅读消息
	 */
	public static boolean readMessage(String messageId, String customerId) throws Exception{
		int result;
		
		String sql = "" +
					" UPDATE" +
					"	" + Constants.TABLE_MESSAGE_RECEIVE + 
					" SET status = " + Constants.MSG_STATUS_READ +
					" WHERE id = " + StringEscapeUtils.escapeSql(messageId) +
					" 	AND type = " + Constants.MSG_TYPE_MERCHANT_TO_CUSTOMER +
					"	AND receiver_id = '" + StringEscapeUtils.escapeSql(customerId) + "'";

		result = DBConn.update(sql);
		
		return (result > 0);
	}
	
	/**
	 * 取得请求详细
	 */
	public static Request getRequestDetail(String requestId, String customerId) throws Exception{
		ResultSet result;
		Request request = null;
		
		String sql = "" +
				" SELECT " +
				"	A.id, A.type, A.request_date, A.request_limit, A.status, A.address, A.request_content," +
				"	B.model, B.displacement, C.mobile " +
				" FROM " + Constants.TABLE_REQUEST + " A " +
				" 	LEFT JOIN " + Constants.TABLE_CAR_INFO + " B " +
				"	ON A.car_license = B.license_number " +
				" 	LEFT JOIN " + Constants.TABLE_CUSTOMER + " C " +
				"	ON A.customer_id = C.user_id " +
				" WHERE A.customer_id = '" + StringEscapeUtils.escapeSql(customerId) + "' " +
				"	AND A.id = " + StringEscapeUtils.escapeSql(requestId) +
				" ORDER BY request_date DESC ";
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			request = new Request();
			request.setId(result.getString("id"));
			request.setType(result.getString("type"));
			request.setRemainTime((result.getLong("request_limit") - System.currentTimeMillis()) + "");
			request.setRequestStatus(result.getString("status"));
			request.setAddress(result.getString("address"));
			request.setContent(result.getString("request_content"));
			request.setCarModel(result.getString("model"));
			request.setDisplacement(result.getString("displacement"));
			request.setCustomerMobile(result.getString("mobile"));
			request.setDate(result.getDate("request_date").toString() + " " + result.getTime("request_date").toString());
		}
		
		return request;
	}
	
	/**
	 * 发表留言
	 */
	public static boolean addRequestMessage(String requestId, String customerId, String message) throws Exception{
		int result;
		
		String sql = "" +
				" INSERT INTO " + Constants.TABLE_REQUEST_MSG +
				" 	(request_id, user_id, message, add_time) " +
				" VALUES (" +
				"	'" + StringEscapeUtils.escapeSql(requestId) + "', " +
				"	'" + StringEscapeUtils.escapeSql(customerId) + "', " +
				"	'" + StringEscapeUtils.escapeSql(message) + "', " +
				"	'" + Utils.getStrTime(System.currentTimeMillis()) + "')";
		
		result = DBConn.update(sql);
		
		if (result > 0) {
			return true;
		}
		
		return false;
	}
	
	/**
	 * 取得留言列表
	 */
	public static Map<String, Object> getRequestMessages(String requestId, int page, int pageSize) throws Exception{
		Map<String, Object> dataMap = new HashMap<String, Object>();
		ResultSet result;
		List<RequestMessage> ret = null;
		RequestMessage message = null;
		String sql = "";
		String countSql = "";
		String limitStr = "";
		
		if (page > 0 && pageSize > 0) {
			limitStr = "   LIMIT " + (page - 1) * pageSize + ","  + pageSize;
		}
		
		sql = "" +
				" SELECT " +
				"	id, request_id, user_id, message, add_time " +
				" FROM " + Constants.TABLE_REQUEST_MSG +
				" WHERE request_id = '" + StringEscapeUtils.escapeSql(requestId) + "' " +
				" ORDER BY add_time DESC " +
				limitStr;
		
		countSql = "" +
				" SELECT " +
				"	count(1) AS total_num " +
				" FROM " + Constants.TABLE_REQUEST_MSG +
				" WHERE request_id = '" + StringEscapeUtils.escapeSql(requestId) + "' ";
		
		result = DBConn.query(sql);
		
		if (result.first()) {
			ret = new ArrayList<RequestMessage>();
			
			do {
				message = new RequestMessage();
				message.setMessageId(result.getString("id"));
				message.setRequestId(result.getString("request_id"));
				message.setUserId(result.getString("user_id"));
				message.setMessage(result.getString("message"));
				message.setAdd_time(result.getDate("add_time").toString() + " " + result.getTime("add_time").toString());
				
				ret.add(message);
			} while (result.next());
		}
		
		dataMap.put("items", ret);
		
		result = DBConn.query(countSql);
		if (result.first()) {
			dataMap.put("total_num", result.getString("total_num"));
		}
		return dataMap;
	}
}
